--
-- Description: Script to save a product record.
-- This includes inserting if product is new or updating if existing product
-- with updated information.
-- Date created: 24 Apr 2008
--

set ANSI_NULLS on
set QUOTED_IDENTIFIER on
go

--
-- drop the stored procedure if it exists and recreate it.
--
if exists
(
	select * 
	from sys.objects
	where object_id = object_id(N'[saveProduct_sp]')
	and type in (N'P', N'PC')
)
begin
    drop procedure [dbo].[saveProduct_sp]
end
go

create procedure [dbo].[saveProduct_sp]
(
    @ProductID int
    , @ProductName nvarchar(50)
	, @ProductVersion nvarchar(50) = null
	, @DateCreated smalldatetime
	, @DateModified smalldatetime
	, @TotalEvents int
)
as
begin
	--
	-- check if the product exist in the table.
	--
	if not exists
	(
		select ProductID
		from dbo.Products
		where ProductID = @ProductID
	)
	begin
		--
		-- create a new record with the new ProductID.
		--
		insert into [dbo].[Products]
		(
			ProductID
			, ProductName
			, ProductVersion
			, DateCreated
			, DateModified
			, TotalEvents
		)
		values
		(
			@ProductID
			, @ProductName
			, @ProductVersion
			, @DateCreated
			, @DateModified
			, @TotalEvents
		)
	end
	else
	begin
		--
		-- Update the record for the DateModified and TotalEvents
		--
		update [dbo].[Products]
		set DateModified = @DateModified
			, TotalEvents = @TotalEvents
		where @DateModified > DateModified
		and ProductID = @ProductID
	end
end
go

set ansi_nulls off
go
set quoted_identifier off
go
